<?php

/**
 * 数据库操作模型基类
 * @author keluo <pycorvn@yeah.net>
 * @since 2012-5-28 11:23:25
 */
class SCModel {
	
	protected $_dsn = null;
	protected $_table = null;

	protected $_semantics;
	
	public function __construct() {
		is_null($this->_dsn) && ($this->_dsn = SCConfig::get('DB_CONNECT'));
		$this->_semantics = new stdClass();
	}
	
	public function dsn($dsn) {
		$this->_dsn = $dsn;
		return $this;
	}
	
	public function reset() {
		$this->resetSemantics();
		return $this;
	}
	
	public function from($table) {
		$this->_table = $table;
		return $this;
	}
	
	public function field($fieldList = '*') {
		if(is_array($fieldList)) {
			$fieldList = implode(', ', $fieldList);
		}
		$this->_semantics->field = $fieldList;
		return $this;
	}
	
	public function where($where) {
		$this->_semantics->where = $where;
		return $this;
	}
	public function limit($start, $num = null) {
		if(is_null($num)) {
			$this->_semantics->limit = $start;
		} else {
			$this->_semantics->limit = $start.', '.$num;
		}
		return $this;
	}
	
	public function order($order) {
		$this->_semantics->order = $order;
		return $this;
	}
	
	public function group($group) {
		$this->_semantics->group = $group;
		return $this;
	}
	
	public function find() {
		$sql = 'SELECT ';
		$sql .= isset($this->_semantics->field) ? $this->_semantics->field : '*';
		$sql .= ' FROM `'.$this->_table.'`';
		
		$sql .= isset($this->_semantics->where) ? ' WHERE '.$this->_semantics->where : '';
		$sql .= isset($this->_semantics->group) ? ' GROUP BY '.$this->_semantics->group : '';
		$sql .= isset($this->_semantics->order) ? ' ORDER BY '.$this->_semantics->order : '';
		$sql .= isset($this->_semantics->limit) ? ' LIMIT '.$this->_semantics->limit : '';
		
		$result = mysql_query($sql, SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)).' in SQL : '.$sql, E_USER_ERROR);
		$data = array();
		if($result) {
			while(false !== ($row = mysql_fetch_object($result))) {
				array_push($data, $row);
			}	
		}
		return $data;
	}
	
	/**
	 *
	 * 分页查询数据
	 */
	public function findPage() {
		$sql = 'SELECT SQL_CALC_FOUND_ROWS';
		$sql .= isset($this->_semantics->field) ? $this->_semantics->field : ' *';
		$sql .= ' FROM `'.$this->_table.'`';
		$sql .= isset($this->_semantics->where) ? ' WHERE '.$this->_semantics->where : '';
		$sql .= isset($this->_semantics->group) ? ' GROUP BY '.$this->_semantics->group : '';
		$sql .= isset($this->_semantics->order) ? ' ORDER BY '.$this->_semantics->order : '';
		$sql .= ' LIMIT '.(SCPaging::getCurrentPage() - 1) * SCPaging::getRecordPerPage().', '.SCPaging::getRecordPerPage();
		
		$result = mysql_query($sql, SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)).' in SQL : '.$sql, E_USER_ERROR);
		$data = array();
		if($result) {
			while(false !== ($row = mysql_fetch_object($result))) {
				array_push($data, $row);
			}	
		}
		
		$result = mysql_query('SELECT FOUND_ROWS() AS numRecord', SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)), E_USER_ERROR);
		$result = $result ? mysql_fetch_object($result) : false;
		SCPaging::setNumRecord($result ? $result->numRecord : 0);
		
		return $data;
	}
	
	public function findOne() {
		$sql = 'SELECT ';
		$sql .= isset($this->_semantics->field) ? $this->_semantics->field : '*';
		$sql .= ' FROM `'.$this->_table.'`';
		$sql .= isset($this->_semantics->where) ? ' WHERE '.$this->_semantics->where : '';
		$sql .= isset($this->_semantics->group) ? ' GROUP BY '.$this->_semantics->group : '';
		$sql .= isset($this->_semantics->order) ? ' ORDER BY '.$this->_semantics->order : '';
		$sql .= ' LIMIT 1';
		
		$result = mysql_query($sql, SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)).' in SQL : '.$sql, E_USER_ERROR);
		return $result ? mysql_fetch_object($result) : false;
	}
	
	public function findScalar() {
		$sql = 'SELECT ';
		$sql .= isset($this->_semantics->field) ? $this->_semantics->field : '*';
		$sql .= ' FROM `'.$this->_table.'`';
		$sql .= isset($this->_semantics->where) ? ' WHERE '.$this->_semantics->where : '';
		$sql .= isset($this->_semantics->group) ? ' GROUP BY '.$this->_semantics->group : '';
		$sql .= isset($this->_semantics->order) ? ' ORDER BY '.$this->_semantics->order : '';
		$sql .= ' LIMIT 1';
		
		$result = mysql_query($sql, SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)).' in SQL : '.$sql, E_USER_ERROR);
		$data = $result ? mysql_fetch_row($result) : false;
		return $data ? $data[0] : false;
	}
	
	public function select($sql) {
		$result = mysql_query($sql, SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)).' in SQL : '.$sql, E_USER_ERROR);
		$data = array();
		if($result) {
			while(false !== ($row = mysql_fetch_object($result))) {
				array_push($data, $row);
			}	
		}
		return $data;
	}
	
	public function selectOne($sql) {
		$result = mysql_query($sql, SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)).' in SQL : '.$sql, E_USER_ERROR);
		return $result ? mysql_fetch_object($result) : false;
	}
	
	/**
	 *
	 * 分页查询数据
	 * @example $this->model->selectPage('file.*, game.`key` FROM file, game WHERE file.log_id = game.id');
	 */
	public function selectPage($query) {
		$sql = 'SELECT SQL_CALC_FOUND_ROWS ';
		$sql .= $query;
		$sql .= ' LIMIT '.(SCPaging::getCurrentPage() - 1) * SCPaging::getRecordPerPage().', '.SCPaging::getRecordPerPage();
		
		$result = mysql_query($sql, SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)), E_USER_ERROR);
		$data = array();
		if($result) {
			while(false !== ($row = mysql_fetch_object($result))) {
				array_push($data, $row);
			}
		}
		
		$result = mysql_query('SELECT FOUND_ROWS() AS numRecord', SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)), E_USER_ERROR);
		$result = $result ? mysql_fetch_object($result) : false;
		SCPaging::setNumRecord($result ? $result->numRecord : 0);
		
		return $data;
	}
	
	public function insert(array $data) {
		if(empty($data)) return;
		if(!isset($data[0])) {
			$arr[0] = $data;
		} else {
			$arr = $data;
		}
		
		$fields = array_keys($arr[0]);
		$values = array();
		foreach($arr as $d) {
			$value = '';
			foreach($this->escapeString($d) as $v) {
				$value .= is_null($v) ? 'NULL, ' : '"'.$v.'", ';
			}
			$values[] = '('.rtrim($value, ', ').')';
		}
		
		$sql = 'INSERT INTO `'.$this->_table.'`';
		$sql .= ' (`'.implode('`, `', $fields).'`)';
		$sql .= ' VALUES '.implode(', ', $values);
		mysql_query($sql, SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)).' in SQL : '.$sql, E_USER_ERROR);
		return mysql_insert_id(SCDatabase::get($this->_dsn));
	}
	
	public function update(array $data, $updateAll = false) {
		$sets = array();
		foreach($data as $field=>$value) {
			if(preg_match('/^\{.*\}$/', $value)) { // 特殊处理标识,如'count' => '{count + 1}'
				$sets[] = '`'.$field.'` = '.substr($this->escapeString($value), 1, -1);
			} else {
				$sets[] = '`'.$field.'` = '.(is_null($value) ? 'NULL' : '"'.$this->escapeString($value)).'"';
			}
		}
		$sql = 'UPDATE `'.$this->_table.'` SET '.implode(', ', $sets);
		if(!isset($this->_semantics->where) && !$updateAll) { // 不允许整表更新，需强制加条件，防止误更新
			trigger_error('updating the whole table is forbidden', E_USER_ERROR);
		} else {
			$sql .= ' WHERE '.$this->_semantics->where;
		}
		$sql .= isset($this->_semantics->order) ? ' ORDER BY '.$this->_semantics->order : '';
		$sql .= isset($this->_semantics->limit) ? ' LIMIT '.$this->_semantics->limit : '';
		
		mysql_query($sql, SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)).' in SQL : '.$sql, E_USER_ERROR);
		return true;
	}
	
	public function delete($deleteAll = false) {
		$sql = 'DELETE FROM `'.$this->_table.'`';
		if(!isset($this->_semantics->where) && !$deleteAll) { // 不允许整表删除，需强制加条件，防止误删
			trigger_error('deleting the whole talbe is forbidden');
		} else {
			$sql .= ' WHERE '.$this->_semantics->where;
		}
		$sql .= isset($this->_semantics->order) ? ' ORDER BY '.$this->_semantics->order : '';
		$sql .= isset($this->_semantics->limit) ? ' LIMIT '.$this->_semantics->limit : '';
		
		mysql_query($sql, SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)).' in SQL : '.$sql, E_USER_ERROR);
		return true;
	}
	
	public function query($sql) {
		mysql_query($sql, SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)).' in SQL : '.$sql, E_USER_ERROR);
	}
	
	
	public function transStart() {
		mysql_query('BEGIN', SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)), E_USER_ERROR);
	}
	
	public function transRollback() {
		mysql_query('ROLLBACK', SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)), E_USER_ERROR);
	}
	
	public function transCommit() {
		mysql_query('COMMIT', SCDatabase::get($this->_dsn)) 
			or trigger_error(mysql_error(SCDatabase::get($this->_dsn)), E_USER_ERROR);
	}
	
	private function resetSemantics() {
		$this->_semantics = new stdClass();
		$this->_table = null;
	}
	
	/**
	 * SQL安全过滤
	 * @param string or array $data
	 */
	protected function escapeString($data) {
		if(is_array($data)) {
			foreach($data as $k=>$v) {
				$data[$k] = is_null($v) ? $v : mysql_escape_string($v);
			}
			return $data;
		} else {
			return is_null($data) ? $data : mysql_escape_string($data);
		}
	}
}